/*
This file is part of BORG.

    BORG is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    BORG is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with BORG; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

Copyright 2003 by Mike Berger
 */

package net.sf.borg.model.db.jdbc;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import javax.swing.JOptionPane;

import net.sf.borg.model.beans.Address;
import net.sf.borg.model.db.BeanDB;


/**
 * AddrJdbcDB class
 * JDBC layer for accessing the addresses table
 * 
 * @author Mike Berger
 *  
 * This class has been modified by UF Software Engineering Summer 2009
 * Developer: Dylan Moore 
 */
public class AddrJdbcDB extends JdbcBeanDB<Address> implements BeanDB<Address>
{

	public AddrJdbcDB(String url) throws Exception
	{
		super( url,"/resource/borg_hsqldb.sql" );
	}

	/**
	 * Removes a specified group name from the groups table in the database.
	 * 
	 * Method created by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore
	 * @param groupName
	 * @throws SQLException
	 */
	public void removeGroup(String groupName) throws SQLException
	{
		PreparedStatement stmt = connection_.prepareStatement( "DELETE FROM groups WHERE groupName = ?");

		stmt.setString(1, groupName);
		stmt.executeUpdate();
	}

	/**
	 * Adds a specified group name to the groups table in the database.
	 * Note: this will associate to an address number of -1, meaning 
	 * no addresses will be associated with this group name after this 
	 * method call.
	 * 
	 * Method created by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore
	 * @param groupName
	 * @throws SQLException
	 */
	public void addGroup(String groupName) throws Exception
	{
		if (groupName.compareTo("") == 0) {
			return;
		}
		//the -1 value for the addrKey represents a group that is not referenced by an address
		PreparedStatement stmt = connection_.prepareStatement( "INSERT INTO groups (groupName, addrKey) " +
		"VALUES ( ?, -1)");

		stmt.setString(1, groupName);
		stmt.executeUpdate();
	}

	/**
	 * Adds an Address object to the database.
	 * 
	 * Method edited by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore
	 * @param addr
	 * @throws Exception
	 */
	public void addObj(Address addr) throws Exception
	{
		writeAddrToDB(addr);  //UF SE 2009 - Code modified to reflect method abstraction       

		/* UF SE 2009 - Start of Additions */
		//delete groups not associated with addresses that this address has
		deleteGroupsNotAssociated(addr);
		
		//write groups for this address to group table
		writeGroupsToDB(addr);
		
		//write addr prefs
		writeAddrPrefsToDB(addr);
		/* UF SE 2009 - End of Additions */

		writeCache( addr );

	}

	private void writeAddrToDB(Address addr) throws SQLException {
		PreparedStatement stmt = connection_.prepareStatement( "INSERT INTO addresses ( address_num, " +
				"first_name, last_name, nickname, email, screen_name, work_phone," + 
				"home_phone, fax, pager, street, city, state, zip, country, company," +
				"work_street, work_city, work_state, work_zip, work_country, webpage, notes, birthday, new, modified, deleted) " +
				" VALUES " +
		"( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");


		stmt.setInt( 1, addr.getKey() );
		stmt.setString( 2, addr.getFirstName() );
		stmt.setString( 3, addr.getLastName());
		stmt.setString( 4, addr.getNickname());
		stmt.setString( 5, addr.getEmail());
		stmt.setString( 6, addr.getScreenName() );
		stmt.setString( 7, addr.getWorkPhone() );
		stmt.setString( 8, addr.getHomePhone() );
		stmt.setString( 9, addr.getFax() );
		stmt.setString( 10, addr.getPager() );
		stmt.setString( 11, addr.getStreetAddress() );
		stmt.setString( 12, addr.getCity() );
		stmt.setString( 13, addr.getState() );
		stmt.setString( 14, addr.getZip() );
		stmt.setString( 15, addr.getCountry() );
		stmt.setString( 16, addr.getCompany() );
		stmt.setString( 17, addr.getWorkStreetAddress() );
		stmt.setString( 18, addr.getWorkCity() );
		stmt.setString( 19, addr.getWorkState() );
		stmt.setString( 20, addr.getWorkZip() );
		stmt.setString( 21, addr.getWorkCountry() );
		stmt.setString( 22, addr.getWebPage() );
		stmt.setString( 23, addr.getNotes() );
		java.util.Date bd = addr.getBirthday();        
		if( bd != null )
			stmt.setDate( 24, new java.sql.Date( bd.getTime()) );
		else
			stmt.setDate(24, null );
		stmt.setInt( 25, toInt( addr.getNew()));
		stmt.setInt( 26, toInt( addr.getModified()));
		stmt.setInt( 27, toInt( addr.getDeleted()));
		stmt.executeUpdate();
	}

	private void writeGroupsToDB(Address addr) throws SQLException {
		PreparedStatement stmt;
		stmt = connection_.prepareStatement( "INSERT INTO groups ( addrKey, " +
		"groupName ) VALUES (?, ?)");
		stmt.setInt( 1, addr.getKey() );

		for(String s : addr.getGroups())
		{
			stmt.setString(2, s);
			stmt.executeUpdate();   
		}
	}

	private void writeAddrPrefsToDB(Address addr) throws SQLException {
		PreparedStatement stmt;
		stmt = connection_.prepareStatement("INSERT INTO addrprefs (addrKey, nickname, " +
				"screenname, homephone, workphone, pager, fax, email, webpage, company, " +
				"birthday, picture ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		stmt.setInt(1, addr.getKey());
		stmt.setInt(2, addr.getPref("nickname").intValue());
		stmt.setInt(3, addr.getPref("screenname").intValue());
		stmt.setInt(4, addr.getPref("homephone").intValue());
		stmt.setInt(5, addr.getPref("workphone").intValue());
		stmt.setInt(6, addr.getPref("pager").intValue());
		stmt.setInt(7, addr.getPref("fax").intValue());
		stmt.setInt(8, addr.getPref("email").intValue());
		stmt.setInt(9, addr.getPref("webpage").intValue());
		stmt.setInt(10, addr.getPref("company").intValue());
		stmt.setInt(11, addr.getPref("birthday").intValue());
		stmt.setInt(12, addr.getPref("picture").intValue());
		stmt.executeUpdate();
	}

	private void deleteGroupsNotAssociated(Address addr) throws SQLException {
		PreparedStatement stmt;
		stmt = connection_.prepareStatement("DELETE FROM groups where groupName = ?" +
				"AND addrkey = -1");
		for (String groupNm : addr.getGroups()) {
			stmt.setString(1, groupNm);
			stmt.execute();
		}
	}

	/**
	 * Deletes an Address (specified by a given key) from the 
	 * database.
	 * 
	 * Method edited by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore
	 * @param key
	 * @throws Exception
	 */
	public void delete(int key) throws Exception
	{
		PreparedStatement stmt = connection_.prepareStatement( "DELETE FROM addresses WHERE address_num = ?" );
		stmt.setInt( 1, key );
		stmt.executeUpdate();
		
		/* UF SE 2009 - Start of Additions */
		stmt = connection_.prepareStatement( "DELETE FROM groups WHERE addrkey = ?" );
		stmt.setInt( 1, key );
		stmt.executeUpdate();
		
		stmt = connection_.prepareStatement( "DELETE FROM addrprefs WHERE addrkey = ?" );
		stmt.setInt( 1, key );
		stmt.executeUpdate();
		/* UF SE 2009 - End of Additions */

		delCache( key );
	}

	public Collection<Integer> getKeys() throws Exception
	{
		ArrayList<Integer> keys = new ArrayList<Integer>();
		PreparedStatement stmt = connection_.prepareStatement("SELECT address_num FROM addresses ORDER BY last_name, first_name" );
		ResultSet rs = stmt.executeQuery();
		while( rs.next() )
		{
			keys.add( new Integer(rs.getInt("address_num")) );
		}

		return( keys );

	}

	public int nextkey() throws Exception
	{
		PreparedStatement stmt = connection_.prepareStatement("SELECT MAX(address_num) FROM addresses" );
		ResultSet r = stmt.executeQuery();
		int maxKey = 0;
		if( r.next() )
			maxKey = r.getInt(1);
		return ++maxKey;
	}

	public Address newObj()
	{
		return( new Address() );
	}

	/**
	 * Constructs a query that will retrieve all 
	 * distinct group names from the groups table in the database.
	 * 
	 * Method created by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore
	 * @throws SQLException
	 */
	private PreparedStatement getGroups() throws SQLException
	{
		PreparedStatement stmt = connection_.prepareStatement("SELECT DISTINCT groupName from groups" );
		return stmt;
	}

	/**
	 * This method returns all the distinct group names from the groups table.
	 * 
	 * Method created by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore
	 * @return ArrayList<String>
	 * @throws SQLException
	 */
	public ArrayList<String> getAllGroups() throws SQLException
	{
		PreparedStatement ps = getGroups();
		ResultSet rs = ps.executeQuery();
		ArrayList<String> ret = new ArrayList<String>(); 
		while(rs.next())
		{
			ret.add(rs.getString("groupName"));
		}
		return ret;

	}

	/**
	 * This method constructs a query to retrieve all 
	 * group names associated with an address key.
	 * 
	 * Method created by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore
	 * @param key
	 * @return PreparedStatement
	 * @throws SQLException
	 */
	PreparedStatement getAllGroups(int key) throws SQLException
	{
		PreparedStatement stmt = connection_.prepareStatement("SELECT * FROM groups WHERE addrKey = ?" );
		stmt.setInt( 1, key );
		return stmt;
	}
	
	PreparedStatement getAddrPrefs(int key) throws SQLException
	{
		PreparedStatement stmt = connection_.prepareStatement("SELECT * FROM addrprefs WHERE addrKey = ?" );
		stmt.setInt( 1, key );
		return stmt;
	}

	/**
	 * Retrieves all Address objects associated with a specified group name.
	 * 
	 * Method created by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore
	 * @param groupName
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<Address> getAddressByGroup(String groupName) throws SQLException
	{
		PreparedStatement ps = getPSOne(groupName);
		ResultSet rs = ps.executeQuery();
		ArrayList<Address> ret = new ArrayList<Address>();

		while(rs.next())
		{
			int addressNum = rs.getInt("addrKey");
			if (addressNum != -1) {
				PreparedStatement stmt = connection_.prepareStatement("SELECT * FROM addresses WHERE address_num = ?" );
				stmt.setInt( 1, addressNum );

				ResultSet addrRS = stmt.executeQuery();
				if (addrRS.next()) {					
					Address currAddr = createFrom(addrRS);

					ret.add(currAddr);
				}
			}
		}

		return ret;
	}

	/**
	 * This method constructs a query for all addresses associated with a group name
	 * 
	 * Method created by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore
	 * @param groupName
	 * @return PreparedStatement
	 * @throws SQLException
	 */
	PreparedStatement getPSOne(String groupName) throws SQLException
	{
		PreparedStatement stmt = connection_.prepareStatement("SELECT * FROM groups WHERE groupName = ?" );
		stmt.setString( 1, groupName);
		return stmt;
	}

	/**
	 * This method constructs a query to find all addresses associated with an address_num == key
	 */
	PreparedStatement getPSOne(int key) throws SQLException
	{
		PreparedStatement stmt = connection_.prepareStatement("SELECT * FROM addresses WHERE address_num = ?" );
		stmt.setInt( 1, key );
		return stmt;
	}

	PreparedStatement getPSAll() throws SQLException
	{
		PreparedStatement stmt = connection_.prepareStatement("SELECT * FROM addresses" );
		return stmt;
	}

	/**
	 * Creates an Address object from a ResultSet object.
	 * 
	 * Method edited by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore 
	 * @param r
	 * @throws SQLException
	 */
	Address createFrom(ResultSet r) throws SQLException
	{
		Address addr = new Address();
		addr.setKey(r.getInt("address_num"));
		addr.setFirstName( r.getString("first_name"));
		addr.setLastName( r.getString("last_name"));
		addr.setNickname( r.getString("nickname"));
		addr.setEmail( r.getString("email"));
		addr.setScreenName( r.getString("screen_name"));
		addr.setWorkPhone( r.getString("work_phone"));
		addr.setHomePhone( r.getString("home_phone"));
		addr.setFax( r.getString("fax"));
		addr.setPager( r.getString("pager"));
		addr.setStreetAddress( r.getString("street"));
		addr.setCity( r.getString("city"));
		addr.setState( r.getString("state"));
		addr.setZip( r.getString("zip"));
		addr.setCountry( r.getString("country"));
		addr.setCompany( r.getString("company"));
		addr.setWorkStreetAddress( r.getString("work_street"));
		addr.setWorkCity( r.getString("work_city"));
		addr.setWorkState( r.getString("work_state"));
		addr.setWorkZip( r.getString("work_zip"));
		addr.setWorkCountry( r.getString("work_country"));
		addr.setWebPage( r.getString("webpage"));
		addr.setNotes( r.getString("notes"));
		addr.setNew( r.getInt("new" ) != 0 );
		addr.setModified( r.getInt("modified" ) != 0 );
		addr.setDeleted( r.getInt("deleted" ) != 0 );
		if( r.getDate("birthday") != null )
			addr.setBirthday( new java.util.Date( r.getDate("birthday").getTime()));

		/* UF SE 2009 - Start of Additions */
		//groups
		PreparedStatement pst = getAllGroups(r.getInt("address_num")); //query and add the groups associated with this address
		ResultSet resultSet = pst.executeQuery();
		while(resultSet.next())
		{
			addr.addToGroup(resultSet.getString("groupName"));
		}
		
		//addr prefs
		pst = getAddrPrefs(addr.getKey());
		resultSet = pst.executeQuery();
		resultSet.next();
		addr.setPref("nickname", new Integer(resultSet.getInt("nickname")));//
		addr.setPref("screenname",  new Integer(resultSet.getInt("screenname")));//
		addr.setPref("homephone",  new Integer(resultSet.getInt("homephone")));
		addr.setPref("workphone",  new Integer(resultSet.getInt("workphone")));
		addr.setPref("pager",  new Integer(resultSet.getInt("pager")));//
		addr.setPref("fax",  new Integer(resultSet.getInt("fax")));//
		addr.setPref("email",  new Integer(resultSet.getInt("email")));
		addr.setPref("webpage",  new Integer(resultSet.getInt("webpage")));
		addr.setPref("company",  new Integer(resultSet.getInt("company")));//
		addr.setPref("birthday",  new Integer(resultSet.getInt("birthday"))); //
		addr.setPref("picture",  new Integer(resultSet.getInt("picture")));//
		/* UF SE 2009 - End of Additions */

		return addr;
	}

	/**
	 * Updates the database information for a specified Address.
	 * 
	 * Method edited by UF Software Engineering Summer 2009
	 * Developer: Dylan Moore 
	 * @param addr
	 * @throws Exception
	 */
	public void updateObj(Address addr) throws Exception
	{

		PreparedStatement stmt = connection_.prepareStatement( "UPDATE addresses SET " +
				"first_name = ?, last_name = ?, nickname = ?, email = ?, screen_name = ?, work_phone = ?," + 
				"home_phone = ?, fax = ?, pager = ?, street = ?, city = ?, state = ?, zip = ?, country = ?, company = ?," +
				"work_street = ?, work_city = ?, work_state = ?, work_zip = ?, work_country = ?, webpage = ?, notes = ?, birthday = ?, new = ?, modified = ?, deleted = ? " +
		" WHERE address_num = ?" );


		stmt.setString( 1, addr.getFirstName() );
		stmt.setString( 2, addr.getLastName());
		stmt.setString( 3, addr.getNickname());
		stmt.setString( 4, addr.getEmail());
		stmt.setString( 5, addr.getScreenName() );
		stmt.setString( 6, addr.getWorkPhone() );
		stmt.setString( 7, addr.getHomePhone() );
		stmt.setString( 8, addr.getFax() );
		stmt.setString( 9, addr.getPager() );
		stmt.setString( 10, addr.getStreetAddress() );
		stmt.setString( 11, addr.getCity() );
		stmt.setString( 12, addr.getState() );
		stmt.setString( 13, addr.getZip() );
		stmt.setString( 14, addr.getCountry() );
		stmt.setString( 15, addr.getCompany() );
		stmt.setString( 16, addr.getWorkStreetAddress() );
		stmt.setString( 17, addr.getWorkCity() );
		stmt.setString( 18, addr.getWorkState() );
		stmt.setString( 19, addr.getWorkZip() );
		stmt.setString( 20, addr.getWorkCountry() );
		stmt.setString( 21, addr.getWebPage() );
		stmt.setString( 22, addr.getNotes() );
		java.util.Date bd = addr.getBirthday();        
		if( bd != null )
			stmt.setDate( 23, new java.sql.Date( bd.getTime()) );
		else
			stmt.setDate(23, null );
		stmt.setInt( 24, toInt( addr.getNew()));
		stmt.setInt( 25, toInt( addr.getModified()));
		stmt.setInt( 26, toInt( addr.getDeleted())); 

		stmt.setInt( 27, addr.getKey() );

		stmt.executeUpdate();

		delCache( addr.getKey() );

		/* UF SE 2009 - Start of Additions */
		//delete groups not associated with addresses that this address has
		deleteGroupsNotAssociated(addr);
		//update groups for this address to group table
		//first we will delete all groups associated with this address
		connection_.setAutoCommit(false);

		stmt = connection_.prepareStatement("DELETE FROM groups WHERE addrKey = ?");
		stmt.setInt(1, addr.getKey());
		stmt.executeUpdate();

		writeGroupsToDB(addr);

		connection_.commit();
		connection_.setAutoCommit(true);	
		
		//update the prefs table
		stmt = connection_.prepareStatement("DELETE FROM addrprefs WHERE addrKey = ?");
		stmt.setInt(1, addr.getKey());
		stmt.executeUpdate();
		
		writeAddrPrefsToDB(addr);
		/* UF SE 2009 - End of Additions */


		writeCache( addr );
	}

}
